<?php

/*
 * This class is used for as Model for Contractor table 
 */

class Model_Contractor extends Model_Base {

    protected $_name = 'tbl_contractor';
    protected $_primaryKey = 'cont_id';

    /* stored value in session when Contractor login */

    public function getContractorAuthData($dataArray = Array()) {
        $select = $this->select()
                ->from(array('lgn' => 'tbl_login'))
                ->setIntegrityCheck(false)
                ->join(array('ctr' => 'tbl_contractor'), "lgn.lgn_id = ctr.lgn_id")
                ->where("ctr.lgn_id = '" . $dataArray['lgn_id'] . "'")
                ->where("lgn.lgn_id = '" . $dataArray['lgn_id'] . "'");
        $data = parent::fetchRow($select);
        $languageModel = new Model_Language();
        $where = 'lang_id=' . $data->lang_id;
        $languagedefaultArray = $languageModel->fetchType($where);

        if ($data->cont_isadmin == 1) {
            $roletype = 'SuperContractor';
        } else {
            $roletype = 'Contractor';
        }
        $object = new stdClass();
        $object->usr_id = $data->cont_id;
        $object->lgn_id = $data->lgn_id;
        $object->lgn_email = $data->lgn_email;
        $object->lgn_type = $data->lgn_type;
        $object->name = $data->cont_firstname . " " . $data->cont_lastname;
        $object->role = "CONTRACTOR";
        $object->roletype = $roletype;
        $object->company_id = $data->com_id;
        $object->lgn_active = $data->lgn_active;
        $object->usr_lang_id = $data->lang_id;
        $object->usr_lang_code = strtolower($languagedefaultArray[0]['lang_code']);
        $object->COMPANY_SESSION_ID = "";
        return $object;
    }

    //get data of contractor if login type CONTRACTOR

    public function getcontractorId($lgn_id) {
        $select = $this->select()
                ->from(array('cont' => 'tbl_contractor'))
                ->setIntegrityCheck(false)
                ->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = cont.lgn_id")
                ->where('cont.lgn_id = ?', $lgn_id)
                ->where('lgn_active != 2');
        // see reasoning in fetchEntries() as to why we return only an array

        $data = $this->fetchRow($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    //get language array
    public function getlanguageArray() {
        $languageModel = new Model_Language();
        $languageArray = $languageModel->language_fetchall();
        $where = 'lang_isdefault=1';
        $languagedefaultArray = $languageModel->fetchType($where);
        $finalArray[$languagedefaultArray[0]['lang_id']] = $languagedefaultArray[0]['lang_name'] . " [" . strtolower($languagedefaultArray[0]['lang_code']) . "]";

        foreach ($languageArray as $com) {
            $finalArray[$com['lang_id']] = $com['lang_name'] . " [" . strtolower($com['lang_code']) . "]";
        }

        return $finalArray;
    }

    //get company array
    public function getcompanyArray($where = '') {
        $companyModel = new Model_Company();
        $companyArray = $companyModel->fetchCompany($where);
        $finalArray[''] = '';
        foreach ($companyArray as $com) {
            $finalArray[$com['com_id']] = $com['com_name'];
        }
        return $finalArray;
    }

    //get department array 
    //getdepartmentArray
    public function getdepartmentArray($where = '') {
        $departmentModel = new Model_Department();
        $departmentArray = $departmentModel->fetchDepartment($where);
        foreach ($departmentArray as $com) {
            $finalArray[$com['dep_id']] = $com['dep_name']. " [".$com['site_name']."]";
        }
        if (!empty($finalArray)) {
            return $finalArray;
        } else {
            return array();
        }
    }

      //Get title data from tbl_list table

    public function gettitleArray($lang_id) {
        $ParentdropdownModel = new Model_Parentlist();
        $ParentdropdownArray = $ParentdropdownModel->fetchall('Salutations_title');
        $finalArray[''] = '-- Select Title --';
        $parent_id = $ParentdropdownArray[0]['parent_id'];
        $ChilddropdownModel = new Model_List();
        $where = 'child.lang_id = '.$lang_id.' and child.parent_id = '.$parent_id.' ';

        $ChilddropdownArray = $ChilddropdownModel->fetchType($where);

        foreach ($ChilddropdownArray as $com) {
            $finalArray[$com['ref_child_id']] = $com['child_name'];
        }
        if (!empty($finalArray)) {
            return $finalArray;
        } else {
            return array();
        }
    }

    
    
    //Get Quality data from tbl_list table

    public function getqualityArray($lang_id) {
        
        $ParentdropdownModel = new Model_Parentlist();
        $ParentdropdownArray = $ParentdropdownModel->fetchall('Quality');
        $finalArray[''] = '-- Select Quality --';
        $parent_id = $ParentdropdownArray[0]['parent_id'];
        $ChilddropdownModel = new Model_List();
        $where = 'child.lang_id = '.$lang_id.' and child.parent_id = '.$parent_id.' ';
        
        $ChilddropdownArray = $ChilddropdownModel->fetchType($where);
        foreach ($ChilddropdownArray as $com) {
            $finalArray[$com['ref_child_id']] = $com['child_name'];
        }
        if (!empty($finalArray)) {
            return $finalArray;
        } else {
            return array();
        }
    }

    //Get availability data from tbl_list table
    public function getavailabilityArray($lang_id) {
       // echo 'in';exit;
        $ParentdropdownModel = new Model_Parentlist();
        $ParentdropdownArray = $ParentdropdownModel->fetchall('Availability');
        $finalArray[''] = '-- Select Availability --';
        $parent_id = $ParentdropdownArray[0]['parent_id'];
        $ChilddropdownModel = new Model_List();
        $where = 'child.lang_id = '.$lang_id.' and child.parent_id = '.$parent_id.' ';
        $ChilddropdownArray = $ChilddropdownModel->fetchType($where);
        foreach ($ChilddropdownArray as $com) {
            $finalArray[$com['ref_child_id']] = $com['child_name'];
        }
        return $finalArray;
    }

    public function getcountryArray() {
        $countryModel = new Model_Country();
        $countryArray = $countryModel->fetchall();
        $finalArray[''] = '';
        foreach ($countryArray as $com) {
            $finalArray[$com['country_id']] = $com['country_name'];
        }
        return $finalArray;
    }

    
    //get contractor list (contractor department = request department)
    public function getDepartmentContractor($where,$where_cust){
      $select = $this->select()
                ->from(array('req' => 'tbl_request'),array('req_id'))
                ->setIntegrityCheck(false)
                ->join(array('subj' => 'tbl_subject'), "req.subj_id = subj.subj_id",array('subj_id'))
                ->join(array('con_dep' => 'tbl_con_department'), "con_dep.dep_id = subj.dep_id",array('dep_id','dep_cont_id'=>'cont_id'))
                ->join(array('cont' => 'tbl_contractor'), "cont.cont_id = con_dep.cont_id",array('cont_firstname','cont_id','cont_lastname'))
                ->join(array('lgn' => 'tbl_login'), "cont.lgn_id = lgn.lgn_id",array('lgn_id'))
                ->where('lgn_active != 2');
        if ($where != '') {
            $select->where($where);
        }
        $select->group('dep_cont_id');
        $data = parent::fetchAll($select);
        $finalArray[''] = 'Select';
        foreach ($data as $com) {
            $finalArray[$com['lgn_id']] = ucfirst($com['cont_firstname'] . " " . $com['cont_lastname'])." [Contractor]";
        }
        
        $customerArray = $this->getDepartmentCustomer($where_cust);
        foreach ($customerArray as $cust_key =>$cust_value) {
            $finalArray[$cust_key] = $cust_value;
        }
        return $finalArray;
    }
    
    
    //get customer list (customer department = request department)
    public function getDepartmentCustomer($where){
      $select = $this->select()
                ->from(array('req' => 'tbl_request'),array('req_id'))
                ->setIntegrityCheck(false)
                ->join(array('subj' => 'tbl_subject'), "req.subj_id = subj.subj_id",array('subj_id'))
                ->join(array('cust_dep' => 'tbl_cust_department'), "cust_dep.dep_id = subj.dep_id",array('dep_id','dep_cust_id'=>'cust_id'))
                ->join(array('cust' => 'tbl_customer'), "cust.cust_id = cust_dep.cust_id",array('cust_firstname','cust_id','cust_lastname'))
                ->join(array('lgn' => 'tbl_login'), "cust.lgn_id = lgn.lgn_id",array('lgn_id'))
                ->where('lgn_active != 2');
        if ($where != '') {
            $select->where($where);
        }
        $select->group('dep_cust_id');
        #echo $select;exit;
        $data = parent::fetchAll($select);
        $finalArray = array();
        
        foreach ($data as $com) {
            $finalArray[$com['lgn_id']] = ucfirst($com['cust_firstname'] . " " . $com['cust_lastname'])." [Customer]";
        }
       
        return $finalArray;
    }
    
    
    
    
    
    
    public function getcontractorArray($lang_id,$where = "") {

        $contractorArray = $this->fetchContractor($lang_id,$where);
        $finalArray[''] = 'Select Contractor';
        if ($where != '') {
            foreach ($contractorArray as $com) {
                $finalArray[$com['cont_id']] = ucfirst($com['cont_firstname'] . " " . $com['cont_lastname']);
            }
        }
        return $finalArray;
    }

    /**
     * Function to return different status of the Contractor
     *
     * @param  null
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function getStatusArray() {
        return array(
            #'' => '-- Select Status --',
            '1' => 'Active',
            '0' => 'Inactive'
        );
    }

    //get data of admin if login type ADMIN

    public function getadminId($lgn_id) {
        $select = $this->select()
                ->from(array('con' => 'tbl_contractor'))
                ->setIntegrityCheck(false)
                ->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = con.lgn_id")
                ->where('con.lgn_id = ?', $lgn_id)
                ->where('lgn_active != 2');
        // see reasoning in fetchEntries() as to why we return only an array

        $data = $this->fetchRow($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    /**
     * Fetch an individual entry for view
     *
     * @param  int|string $id
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function fetchEntryByViewId($id,$lang_id) {
        //array('child_quality' => 'child_name')        
        $select = $this->select()
                ->setIntegrityCheck(false)
                ->from(array('con' => 'tbl_contractor'))
                ->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = con.updated_lgn_id")
                ->join(array('lgn_const' => 'tbl_login'), "lgn_const.lgn_id = con.lgn_id", array('lgn_const_email' => 'lgn_email'))
                ->join(array('country' => 'tbl_country'), "country.country_id = con.cont_country")
                ->join(array('company' => 'tbl_company'), "company.com_id = con.com_id")
                ->joinLeft(array('title' => 'tbl_list'), "title.ref_child_id = con.cont_title and title.lang_id = ".$lang_id."", array('cont_title_child' => 'child_name'))
                ->joinLeft(array('quality' => 'tbl_list'), "quality.ref_child_id = con.cont_quality and quality.lang_id = ".$lang_id."", array('child_quality' => 'child_name'))
                ->joinLeft(array('avail' => 'tbl_list'), "avail.ref_child_id = con.cont_availability and avail.lang_id = ".$lang_id."", array('child_availability' => 'child_name'))
                ->where('cont_id = ?', $id)
                ->where('lgn.lgn_active != 2');
        #echo $select;exit;
        $data = $this->fetchRow($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    public function fetchEntryById($id) {
        $select = $this->select()
                ->from(array('con' => 'tbl_contractor'))
                ->setIntegrityCheck(false)
                ->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = con.lgn_id")
                ->join(array('country' => 'tbl_country'), "country.country_id = con.cont_country")
                ->where('cont_id = ?', $id)
                ->where('lgn_active != 2');

        $data = $this->fetchRow($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    /**
     *  This function is used to get contractors
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function fetchContractor($lang_id,$where = "", $limit = "", $offset = "0", $order = 'cont_id', $sort = 'ASC') {
        //echo $where;exit;
        $select = $this->select()
                ->from(array('con' => 'tbl_contractor'), Array('*', 'contractor_name' => "CONCAT(cont_firstname , ' ', cont_lastname)"))
                ->setIntegrityCheck(false)
                ->joinLeft(array('title' => 'tbl_list'), "title.child_id = con.cont_title and title.lang_id = ".$lang_id."", array('cont_title_child' => 'child_name'))
                ->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = con.lgn_id")
                ->join(array('cmp' => 'tbl_company'), "cmp.com_id = con.com_id")
                ->where('lgn_active != 2');
        if ($where != '') {
            $select->where($where);
        }

        if ($limit != "") {
            $select->limit($limit, $offset);
        }
        $select->group('cont_id');
        $select->order($order . " " . $sort);
        #echo $select;exit;
        $data = parent::fetchAll($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    /**
     *  This function is used to get total rows
     * @return int
     */
    public function getContractorCount($lang_id,$where) {
        $select = $this->select()
                ->from(array('con' => 'tbl_contractor'), Array('cnt' => 'count(*)'))
                ->setIntegrityCheck(false)
                ->joinLeft(array('title' => 'tbl_list'), "title.child_id = con.cont_title and title.lang_id = ".$lang_id."", array('cont_title_child' => 'child_name'))
                ->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = con.lgn_id")
                ->join(array('cmp' => 'tbl_company'), "cmp.com_id = con.com_id")
                ->where('lgn_active != 2');
        if ($where != '') {
            $select->where($where);
        }
        $rows = parent::fetchRow($select);
        return($rows->cnt);
    }

    /**
     *  This function is used to delete contractors
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function deleteContractor($prdtArray) {
        // echo 'in';exit;
        $config = Zend_Registry::get('config');
        $loginModel = new Model_Login();
        if (is_array($prdtArray)) {
            foreach ($prdtArray as $_contractor) {
                $contractorData = Array();
                $contractorData = $this->fetchEntryById($_contractor);
                if (count($contractorData) > 0) {
                    //update status in login table 
                    $login_delete = $loginModel->deleteloginid($contractorData['lgn_id']);
                    //delete of the images
                    $folder = $config->UPLOAD_PATH . "contractor/";
                    $contractor_image = $folder . $contractorData['cont_image'];
                    if (file_exists($contractor_image)) {
                        @unlink($contractor_image);
                    }
                }
            }
        } else {
            $cont_id = $prdtArray;
            $contractorData = $this->fetchEntryById($cont_id);
            if (count($contractorData) > 0) {
                //update status in login table 
                $login_delete = $loginModel->deleteloginid($contractorData['lgn_id']);
                $folder = $config->UPLOAD_PATH . "contractor/";
                //delete of the image
                $contractor_image = $folder . $contractorData['cont_image'];
                if (file_exists($contractor_image)) {
                    @unlink($contractor_image);
                }
            }
        }
    }

    //Get request of constractor
    public function getSitedashboard($companyId,$where, $limit, $offset, $order, $sort,$loggedData) {
        //echo '<pre>';print_r($loggedData);exit;
        
        $requestData = array();
        $requestModel = new Model_Request();
        $siteModel = new Model_Site();
        //  $where_site = "site.com_id = ".$companyId;
        $siteData = $siteModel->fetchEntryByCompanyId($companyId);
        
        $site_id = implode(",", $siteData);
        if ($companyId != '' && $loggedData['roletype'] == 'SuperContractor') {
            $where .= " and request.site_id IN (" . $site_id . ")";
        } else if ($companyId != '' && $loggedData['roletype'] == 'Contractor') { 
            $where .= " and request.site_id IN (" . $site_id . ")  AND ass.ass_lgn_id = ".$loggedData['loggeduserid'];
        } else {
            $where .= "";
        }
        
        $requestData = $requestModel->fetchRequestWithassign($where, $limit, $offset, $order, $sort);
        //  echo 'in';exit;
        return $requestData;
    }

    //Get no of request of constractor
    public function getcountreqdashboard($companyId,$where) {
        $requestData = array();
        $requestModel = new Model_Request();
        $siteModel = new Model_Site();
        //  $where_site = "site.com_id = ".$companyId;
        $siteData = $siteModel->fetchEntryByCompanyId($companyId);
        if ($companyId != '') {
            $site_id = implode(",", $siteData);
            $where .= " and request.site_id IN ('" . $site_id . "')";
        } else {
            $where .= "";
        }
        $requestData = $requestModel->getRequestWithassignCount($where);
        return $requestData;
    }

}
